package com.csair.seam.infrastructure.poi.utils;

import java.io.*;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.codec.binary.Base64;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import com.csair.seam.infrastructure.poi.config.ExcelExportConfig;
import com.csair.seam.infrastructure.poi.config.ExcelImportConfig;
import com.csair.seam.infrastructure.poi.entity.ExcelImportResult;
import com.csair.seam.infrastructure.poi.exception.ExcelException;
import com.csair.seam.infrastructure.poi.service.ExcelImportService;
import com.csair.seam.infrastructure.poi.service.ExportExcelService;
import com.csair.seam.infrastructure.poi.service.impl.ExcelImportServiceImpl;
import com.csair.seam.infrastructure.poi.service.impl.ExportExcelServiceImpl;

import lombok.extern.slf4j.Slf4j;

/**
 * Excel 工具
 * @author 蓝润利
 *
 */
@Slf4j
public class ExcelPoiUtil {
	private static final String EXCEL_DIR = "classpath:/excel/";
	private ExcelPoiUtil() {
	}

	private static void checkFile(MultipartFile importExcelFile) {
		if(importExcelFile == null || importExcelFile.isEmpty()) {
			throw new ExcelException("上传的文件对象为空");
		}
		boolean isExcelFile = FilenameUtils.isExtension(importExcelFile.getOriginalFilename(), new String[] {"xls","xlsx","XLS","XLSX"});
		if(!isExcelFile) {
			throw new ExcelException("文件出错，非excel文件");
		}
		try (InputStream in = importExcelFile.getInputStream()){
			Workbook book = WorkbookFactory.create(in);
			if(book.getNumberOfSheets()>1){
				throw new ExcelException("请检查sheet，只能有一个sheel");
			}
		}catch (IOException e){
			e.printStackTrace();
		}

	}

	/**
	 * 导入Excel
	 * @author 蓝润利
	 * @param importExcelFile MultipartFile文件对象
	 * @param pojoClass 含有@ImportField的Class<T>
	 * @return
	 */
	public static <T> ExcelImportResult<T> importExcel(MultipartFile importExcelFile, Class<T> pojoClass){
		checkFile(importExcelFile);
		return importExcel(importExcelFile, pojoClass,new ExcelImportConfig());
	}

	/**
	 * 导入Excel
	 * @author 蓝润利
	 * @param importExcelFile MultipartFile文件对象
	 * @param pojoClass 含有@ImportField的Class<T>
	 * @param config 导入配置
	 * @return
	 */
	public static <T> ExcelImportResult<T> importExcel(MultipartFile importExcelFile, Class<T> pojoClass,ExcelImportConfig config){
		checkFile(importExcelFile);
		try(InputStream in = importExcelFile.getInputStream()) {
			ExcelImportService importService = new ExcelImportServiceImpl(config);
			return importService.importExcel(in, pojoClass);
		} catch (Exception e) {
			throw new ExcelException(e);
		}
	}

	/**
	 * 导出Excel
	 * @author 蓝润利
	 * @param excelFileName 导出的文件名（带扩展名：*****.xls或*****.xlsx）
	 * @param dataList 数据结果集
	 * @param queryParams 查询参数
	 * @param request
	 * @param response
	 */
	public static <T> void exportExcel(String excelFileName,List<T> dataList,Object queryParams,HttpServletRequest request,HttpServletResponse response) {
		exportExcel(excelFileName, dataList, queryParams, new ExcelExportConfig(), request, response);
	}

	/**
	 * 导出Excel
	 * @author 蓝润利
	 * @param excelFileName 导出的文件名（带扩展名：*****.xls或*****.xlsx）
	 * @param dataList 数据结果集
	 * @param queryParams 查询参数
	 * @param config 导出配置
	 * @param request
	 * @param response
	 */
	public static <T> void exportExcel(String excelFileName,List<T> dataList,Object queryParams,ExcelExportConfig config,HttpServletRequest request,HttpServletResponse response) {
		OutputStream out = null;
		try {
			setResponse(excelFileName, request, response);
			out = response.getOutputStream();
			ExportExcelService exportService = new ExportExcelServiceImpl(config,excelFileName.endsWith(".xlsx"));
			exportService.exportExcel(dataList, queryParams, out);
		} catch (Exception e) {
			throw new ExcelException(e);
		}finally {
			IOUtils.closeQuietly(out);
		}
	}

	/**
	 * 下载Excel模板
	 * @author 蓝润利
	 * @param excelFileName 模板文件名（*****.xls或*****.xlsx）
	 * @param clazz 含有@ImportField的Class<T>
	 * @param request
	 * @param response
	 */
	public static void downloadTemplate(String excelFileName,Class<?> clazz,HttpServletRequest request,HttpServletResponse response) {
		OutputStream out = null;
		try {
			setResponse(excelFileName, request, response);
			out = response.getOutputStream();
			ExportExcelService exportService = new ExportExcelServiceImpl(new ExcelExportConfig(),excelFileName.endsWith(".xlsx"));
			exportService.exportTemplate(clazz, out);
		} catch (Exception e) {
			throw new ExcelException(e);
		}finally {
			IOUtils.closeQuietly(out);
		}
	}

	/**
	 * @description 下载resources目录下的Excel模板
	 * @param resourceFileName Excel模板文件名
	 * @param targetFileName 下载的文件名
	 * @param response
	 */
	public static void downloadTemplateFromResource(String templateFileName,String downloadFileName,HttpServletRequest request,HttpServletResponse response) {
		OutputStream out = null;
		Workbook workbook = null;
		try {
			File file = ResourceUtils.getFile(EXCEL_DIR+templateFileName);
			setResponse(downloadFileName, request, response);
			workbook = WorkbookFactory.create(file);
			out = response.getOutputStream();
			out.flush();
			workbook.write(out);
		} catch (FileNotFoundException e) {
			log.error("{}目录下未发现模板文件：{}",EXCEL_DIR,templateFileName);
		} catch (IOException e) {
			log.error(e.getMessage(),e);
		}finally {
			IOUtils.closeQuietly(out);
			try {
				if (workbook != null) {
					workbook.close();
				}
			} catch (IOException e) {
				log.error(e.getMessage(),e);
			}
		}
	}

	private static void setResponse(String excelFileName,HttpServletRequest request,HttpServletResponse response) throws IOException {
		String agent = request.getHeader("user-agent");
		String downloadFilename = encodeDownloadFilename(excelFileName, agent);
		response.setContentType("application/vnd.ms-excel;charset=UTF-8");
		response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
		response.addHeader("Content-Disposition", "attachment;filename=" + downloadFilename);
	}

	private static String encodeDownloadFilename(String filename, String agent)
			throws IOException {
		if (agent.contains("Firefox")) { // 火狐浏览器
			filename = "=?UTF-8?B?"
					+ Base64.encodeBase64String(filename.getBytes("utf-8"))
					+ "?=";
			filename = filename.replaceAll("\r\n", "");
		} else { // IE及其他浏览器
			filename = URLEncoder.encode(filename, "utf-8");
			filename = filename.replace("+", " ");
		}
		return filename;
	}

	/**
	 * 检查 excel 数据类型
	 * @param file spring file上传到对象
	 */
	public void checkType(MultipartFile file){
		try {
			Workbook workbook = WorkbookFactory.create(file.getInputStream());
			Sheet sheet = workbook.getSheetAt(0);

			if(sheet == null){
				throw new ExcelException("Excel不能是null");
			}

		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获取Sheet页内容
	 * @return
	 */
	public List<Object[]> listFromSheet(Sheet sheet){
		List<Object[]> list = new ArrayList<>();
		for(int i = sheet.getFirstRowNum(); i < sheet.getLastRowNum();i++){
			Row row =sheet.getRow(i);
			if( row == null || row.getPhysicalNumberOfCells() == 0) continue;
			for( int c = row.getFirstCellNum(); c <= row.getLastCellNum();c++){
				Cell cell =row.getCell(c);
				if(cell == null) continue;

			}
		}
		return null;
	}
}
